﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Entity;
using BLL;
using BLL.Access;
using System.Data;
using Platform.Http;
using Web.Class;
using System.IO;
using System.Data.Odbc;
using System.Data.OleDb;
using DevExpress.Web;

namespace Web.Pages.QuShui.DiXiaS._8.QiTaGL
{
    public partial class YonghuYJDZDY : BasePage
    {
        #region 属性
        private DataTable dtSelect;
        #endregion

        #region 变量
        QS_YG_YONGHUXXBLL yhbll = new QS_YG_YONGHUXXBLL();
        #endregion

        #region 方法
        private void InitControl()
        { 
            
        }
        private void ChaXun()
        {
            string yonghuh = txtYongHuH.Text.Replace("'","").Trim();
            string where = "  S_YongHuH like '%{0}%' ";
            where = string.Format(where,yonghuh);
            dtSelect = yhbll.SelectQS_YG_YONGHUXXesDynamic(where," S_YONGHUH");
            gvList.DataSource = dtSelect;
            gvList.DataBind();

        }
        private DataTable GetDataTable(string filePath)
        {

            DataTable retdt = new DataTable();
            retdt.Columns.Add("S_YONGHUH");
            retdt.Columns.Add("S_YONGHUM");
            retdt.Columns.Add("S_DIZHI");
            retdt.Columns.Add("S_YOUBIAN");
            try
            {
                DataTable dtData = ReadExcel(filePath); ;

                if (dtData != null && dtData.Rows.Count > 0)
                {
                    foreach (DataRow dr in dtData.Rows)
                    {
                        DataRow newdr = retdt.NewRow();
                        foreach (DataColumn c in dtData.Columns)
                        {
                            if (c.ColumnName == "S_YONGHUH" || c.ColumnName == "用户号")
                            {
                                newdr["S_YONGHUH"] = dr[c.ColumnName];
                            }
                            if (c.ColumnName == "S_YONGHUM" || c.ColumnName == "用户名")
                            {
                                newdr["S_YONGHUM"] = dr[c.ColumnName];
                            }
                            if (c.ColumnName == "S_DIZHI" || c.ColumnName == "用户地址")
                            {
                                newdr["S_DIZHI"] = dr[c.ColumnName];
                            }
                            if (c.ColumnName == "S_YOUBIAN" || c.ColumnName == "邮政编码")
                            {
                                newdr["S_YOUBIAN"] = dr[c.ColumnName];
                            }
                        }
                        retdt.Rows.Add(newdr);
                    }
                }
            }
            catch (Exception ex)
            {
                PageTools.AlertMsg(this, "文件导入失败：" + ex.Message);
            }
            return retdt;
        }
        private DataTable ReadExcel(string ExcelFilePath)
        {
            DataTable dt = new DataTable();
            //string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " +"Data Source=" +ExcelFilePath + ";Extended Properties=Excel 8.0";
            string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES';data source=" + ExcelFilePath;
            OleDbConnection Connection = new OleDbConnection(strConnection);
            Connection.Open();
            string CommandText = "";
            DataTable dtsheetName = GetWorkBookName(Connection);
            if (dtsheetName != null && dtsheetName.Rows.Count > 0)
            {
                CommandText = "select * from [" + dtsheetName.Rows[0]["TABLE_NAME"].ToString() + "]";
            }
            else
            {
                return null;
            }
            OleDbCommand comm = new OleDbCommand(CommandText, Connection);

            try
            {

                OleDbDataAdapter da = new OleDbDataAdapter(comm);
                DataSet Ds = new DataSet();
                da.Fill(Ds);
                dt = Ds.Tables[0];
            }
            catch (Exception)
            {
            }
            finally
            {
                if (Connection.State == ConnectionState.Open) Connection.Close();
            }
            dt.Columns.Add("选择", typeof(bool));
            return dt;

        }
        /// <summary>
        /// 获取Excel中的所有工作簿
        /// </summary>
        /// <param name="Connection">OleDbConnection对象</param>
        /// <returns></returns>
        public static DataTable GetWorkBookName(OleDbConnection Connection)
        {
            DataTable Dt = null;
            try
            {
                if (Connection.State == ConnectionState.Closed) Connection.Open();
                Dt = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            }
            catch (Exception)
            {
            }
            finally
            {
                if (Connection.State == ConnectionState.Open) Connection.Close();
            }
            return Dt;
        }

      
        private DataTable GetPrintTable()
        {
            
            DataTable alldt = new DataTable();
            alldt.Columns.Add("用户号");
            alldt.Columns.Add("用户名");
            alldt.Columns.Add("用户地址");
            alldt.Columns.Add("邮政编码");
            for (int i = 0; i < gvList.VisibleRowCount; i++)
            {
                HiddenField yonghuh = (HiddenField)gvList.FindRowCellTemplateControl(i, (GridViewDataColumn)gvList.Columns[0], "hid");
                HiddenField yonghum = (HiddenField)gvList.FindRowCellTemplateControl(i, (GridViewDataColumn)gvList.Columns[0], "S_YONGHUM");
                HiddenField dizhi = (HiddenField)gvList.FindRowCellTemplateControl(i, (GridViewDataColumn)gvList.Columns[0], "S_DIZHI");
                HiddenField youbian = (HiddenField)gvList.FindRowCellTemplateControl(i, (GridViewDataColumn)gvList.Columns[0], "S_YOUBIAN");

                ASPxCheckBox cb = (ASPxCheckBox)gvList.FindRowCellTemplateControl(i, (GridViewDataColumn)gvList.Columns[0], "cid");
                if (cb.Checked)
                {
                    DataRow newdr = alldt.NewRow();
                    newdr["用户号"] = yonghuh.Value;
                    newdr["用户名"] = yonghum.Value;
                    newdr["用户地址"] = dizhi.Value;
                    newdr["邮政编码"] = youbian.Value;
                    alldt.Rows.Add(newdr);
                }
            }
            DataTable dt = new DataTable();
            for (int i = 0; i < 16; i++)
            {
                dt.Columns.Add("用户号" + i.ToString());
                dt.Columns.Add("用户名" + i.ToString());
                dt.Columns.Add("用户地址" + i.ToString());
                dt.Columns.Add("邮政编码" + i.ToString());
            }
            DataRow dr = dt.NewRow();
            DataRow[] drs = alldt.Select();
            int inum = drs.Length % 16;
            if (inum > 0)
            {
                for (int i = inum; i < 16; i++)
                {
                    DataRow dr1 = alldt.NewRow();
                    
                    dr1["用户号"] = "";
                    alldt.Rows.Add(dr1);
                }
                drs = alldt.Select();
            }
            for (int i = 0; i < drs.Length; i++)
            {
                int inums = i % 16;
                if (inums == 0)
                    dr = dt.NewRow();
                dr["用户号" + inums.ToString()] = drs[i]["用户号"];
                dr["用户名" + inums.ToString()] = drs[i]["用户名"];
                dr["用户地址" + inums.ToString()] = drs[i]["用户地址"];
                dr["邮政编码" + inums.ToString()] = drs[i]["邮政编码"];
                if (inums == 15)
                    dt.Rows.Add(dr);
            }
            DataRow[] drNull = alldt.Select("用户号='' ");
            for (int i = 0; i < drNull.Length; i++)
            {
                alldt.Rows.Remove(drNull[i]);
            }
            return dt;
        }
        #endregion

        #region 事件
        protected void UpLoadFile_FileUploadComplete(object sender, DevExpress.Web.FileUploadCompleteEventArgs e)
        {

            if (e.UploadedFile.IsValid)
            {
                string wjmc = e.UploadedFile.FileName;
                string filePath = Path.Combine(Server.MapPath("/UPLOAD/QTGL"), wjmc.Replace(Path.GetExtension(e.UploadedFile.FileName), "") + Path.GetExtension(e.UploadedFile.FileName));    //文件完整物理路径
                e.UploadedFile.SaveAs(filePath);

                e.CallbackData = e.UploadedFile.FileName + "|" + filePath;
                return;
            }
            else
            {                
                e.CallbackData = "-1";
                return;
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                dtSelect = new DataTable();
            }
        }

        protected void BtnSearch_Click(object sender, EventArgs e)
        {
            ChaXun();
        }
        protected void BtnXZ_Click(object sender, EventArgs e)
        {
            int end = -1;
            for (int i = 0; i < gvList.VisibleRowCount; i++)
            {
                HiddenField hid = (HiddenField)gvList.FindRowCellTemplateControl(i, (GridViewDataColumn)gvList.Columns[0], "hid");

                ASPxCheckBox cb = (ASPxCheckBox)gvList.FindRowCellTemplateControl(i, (GridViewDataColumn)gvList.Columns[0], "cid");
                cb.Checked = false;
            }
            if (int.TryParse(txtEndNo.Text, out end) && txtStartNo.Text.Trim().Length > 0)
            {
                int num = 1;
                bool ks = false;
                for (int i = 0; i < gvList.VisibleRowCount; i++)
                {
                    HiddenField hid = (HiddenField)gvList.FindRowCellTemplateControl(i, (GridViewDataColumn)gvList.Columns[0], "hid");

                    ASPxCheckBox cb = (ASPxCheckBox)gvList.FindRowCellTemplateControl(i, (GridViewDataColumn)gvList.Columns[0], "cid");
                    if (hid.Value.Trim() == txtStartNo.Text.Trim()||ks)
                    {
                        ks = true;
                        cb.Checked = true;
                        num++;
                        if (num > end)
                        {
                            break;
                        }
                    }
                }
            }
            else if (txtStartNo1.Text.Trim().Length > 0)
            {
                int num = 0;
                bool ks = false;
                for (int i = 0; i < gvList.VisibleRowCount; i++)
                {
                    HiddenField hid = (HiddenField)gvList.FindRowCellTemplateControl(i, (GridViewDataColumn)gvList.Columns[0], "hid");

                    ASPxCheckBox cb = (ASPxCheckBox)gvList.FindRowCellTemplateControl(i, (GridViewDataColumn)gvList.Columns[0], "cid");
                    if (hid.Value.Trim() == txtStartNo1.Text.Trim()||ks)
                    {
                        cb.Checked = true;
                        ks = true;
                        num++;
                        
                    }
                }
            }
        }
        protected void btnDaoRu_Click(object sender, EventArgs e)
        {
            if (!HidList.Contains("path"))
            {
                PageTools.AlertMsg(this,"请上传文件");
                return;
            }
            string filePath = HidList.Get("path").ToString(); 
            if (filePath.Trim().Length == 0)
            {
                PageTools.AlertMsg(this, "请上传文件");
                return;
            }
            dtSelect = GetDataTable(filePath);
            gvList.DataSource = dtSelect;
            gvList.DataBind();
            HidList.Set("path", "");
            txtPath.Text = "";
        }
        protected void btnQuanXuan_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < gvList.VisibleRowCount; i++)
            {
                ASPxCheckBox cb = (ASPxCheckBox)gvList.FindRowCellTemplateControl(i, (GridViewDataColumn)gvList.Columns[0], "cid");
                cb.Checked = true;
            }
        }
        protected void btnFanXuan_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < gvList.VisibleRowCount; i++)
            {
                ASPxCheckBox cb = (ASPxCheckBox)gvList.FindRowCellTemplateControl(i, (GridViewDataColumn)gvList.Columns[0], "cid");
                cb.Checked = !cb.Checked;
            }
        }
        protected void BtnYuLan_Click(object sender, EventArgs e)
        {
            DataTable dt = GetPrintTable();
        }
        protected void BtnDaYin_Click(object sender, EventArgs e)
        {
            DataTable dt = GetPrintTable();
        }
        #endregion
       
    }
}